﻿----------------------
-- SPs for Channel --
----------------------

IF OBJECT_ID(N'[dbo].[CreateChannel]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[CreateChannel];
GO

CREATE PROCEDURE [CreateChannel]
(
    @devId nvarchar(63),
    @devChn smallint,
    @equipId nvarchar(63),
    @equipPhase tinyint,
    @key int out
) WITH ENCRYPTION 
AS 
BEGIN
    SET NOCOUNT ON
    SET @key = (SELECT [Key] FROM ChannelTable WHERE
        DevId = @devId
        AND DevChn = @devChn
        AND EquipId = @equipId
        AND EquipPhase = @equipPhase)
    
    IF @key IS NULL
    BEGIN
        INSERT INTO ChannelTable VALUES
        (
            @devId,
            @devChn,
            @equipId,
            @equipPhase
        )
        set @key = @@IDENTITY
    END
END
GO

IF OBJECT_ID(N'[dbo].[ListChannels]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListChannels];
GO

CREATE PROCEDURE [ListChannels]  WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT *
    FROM ChannelTable
    RETURN
END
GO

----------------------
-- SPs for PDTable --
----------------------

IF OBJECT_ID(N'[dbo].[CreatePD]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[CreatePD];
GO

CREATE PROCEDURE [CreatePD]
(
    @devId nvarchar(63),
    @devChn smallint,
    @equipId nvarchar(63),
    @equipPhase tinyint,
    @pdTime datetime,
    @pdMean real,
    @pdMax real,
    @pdPhase real,
    @pdRep real,
    @pdType nvarchar(63),
    @pdTypeProb nvarchar(63),
    @pdLoc nvarchar(63),
    @pdLevel nvarchar(63),
    @pdFile nvarchar(255),
    @prpsGraph varbinary(MAX),
    @key bigint out
)  WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @channelKey int

    EXEC CreateChannel 
        @devId, 
        @devChn, 
        @equipId, 
        @equipPhase, 
        @channelKey OUTPUT

    INSERT INTO PDTable
    VALUES (
        @channelKey,
        @pDTime,
        @pDMean,
        @pDMax,
        @pDPhase,
        @pDRep,
        @pDType,
        @pDTypeProb,
        @pDLoc,
        @pDLevel,
        @pDFile,
        @pRPSGraph
    )
    SET @key = @@IDENTITY
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListTimePointsOfChannel]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListTimePointsOfChannel];
GO

CREATE PROCEDURE [ListTimePointsOfChannel]
(
    @channelKey int
) WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT [Key], PDTime FROM PDTable WHERE
        [ChannelKey] = @channelKey
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListSignalsOfChannel]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListSignalsOfChannel];
GO

CREATE PROCEDURE [ListSignalsOfChannel]
(
    @channelKey int
) WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT PDTable.[Key]
      ,[ChannelKey]
      ,[PDTime]
      ,[PDMean]
      ,[PDMax]
      ,[PDPhase]
      ,[PDRep]
      ,[PDType]
      ,[PDTypeProb]
      ,[PDLoc]
      ,[PDLevel]
      ,[PDFile]
      ,CT.DevId
      ,CT.DevChn
      ,CT.EquipId
      ,CT.EquipPhase 
      FROM PDTable
    LEFT JOIN [ChannelTable] CT ON PDTable.ChannelKey = CT.[Key] 
	WHERE
        PDTable.[ChannelKey] = @channelKey
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[GetSignal]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[GetSignal];
GO

CREATE PROCEDURE [GetSignal]
(
    @key bigint
)  WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT PDTable.*, CT.DevId, CT.DevChn, CT.EquipId, CT.EquipPhase FROM PDTable
    LEFT JOIN [ChannelTable] CT ON PDTable.ChannelKey = CT.[Key]
    WHERE
        PDTable.[Key] = @key
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[GetLatestChannel]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[GetLatestChannel];
GO

CREATE PROCEDURE [dbo].[GetLatestChannel] WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT CT.[Key], CT.DevId, CT.DevChn, CT.EquipId, CT.EquipPhase FROM PDTable
    LEFT JOIN [ChannelTable] CT ON PDTable.ChannelKey = CT.[Key]
    WHERE
        PDTable.[PDTime] = (SELECT MAX(PDTime) From PDTable)
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListSignals]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListSignals];
GO

CREATE PROCEDURE [ListSignals] WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT PDTable.[Key]
      ,[ChannelKey]
      ,[PDTime]
      ,[PDMean]
      ,[PDMax]
      ,[PDPhase]
      ,[PDRep]
      ,[PDType]
      ,[PDTypeProb]
      ,[PDLoc]
      ,[PDLevel]
      ,[PDFile]
      ,CT.DevId
      ,CT.DevChn
      ,CT.EquipId
      ,CT.EquipPhase 
      FROM PDTable
    LEFT JOIN [ChannelTable] CT ON PDTable.ChannelKey = CT.[Key]
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListSignalsByChannelAndTime]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListSignalsByChannelAndTime];
GO

CREATE PROCEDURE [ListSignalsByChannelAndTime]
(
    @channelKey int,
    @startTime datetime,
    @endTime datetime
) WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT PDTable.[Key]
      ,[PDTime]
      ,[PDMean]
      ,[PDMax]
      ,[PDPhase]
      ,[PDRep]
      ,[PDType]
      ,[PDTypeProb]
      ,[PDLoc]
      ,[PDLevel]
      ,[PDFile]
    FROM PDTable
    WHERE 
        [ChannelKey] = @channelKey
        AND [PDTime] BETWEEN @startTime AND @endTime

    RETURN
END
GO

-------------------------
-- SPs for AlarmTable --
-------------------------

IF OBJECT_ID(N'[dbo].[CreateAlarm]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[CreateAlarm];
GO

CREATE PROCEDURE [CreateAlarm]
(
    @devId nvarchar(63),
    @devChn smallint,
    @equipId nvarchar(63),
    @equipPhase tinyint,
    @alarmTime datetime,
    @alarmType nvarchar(63),
    @alarmLevel tinyint,
    @alarmValue real,
    @key bigint OUT
) WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @channelKey int

    EXEC CreateChannel 
        @devId, 
        @devChn, 
        @equipId, 
        @equipPhase, 
        @channelKey OUTPUT

    INSERT INTO AlarmTable
    VALUES
    (
        @channelKey,
        @alarmTime,
        @alarmType,
        @alarmLevel,
        @alarmValue
    )

    SET @key = @@IDENTITY
    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListAlarmsAfter]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListAlarmsAfter];
GO

CREATE PROCEDURE [ListAlarmsAfter]
(
    @key bigint
) WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT AlarmTable.*
      ,CT.DevId
      ,CT.DevChn
      ,CT.EquipId
      ,CT.EquipPhase
    FROM [AlarmTable]
    LEFT JOIN [ChannelTable] CT ON AlarmTable.ChannelKey = CT.[Key]
    WHERE
        AlarmTable.[Key] > @key

    RETURN
END
GO

IF OBJECT_ID(N'[dbo].[ListLastAlarmForEachChannel]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[ListLastAlarmForEachChannel];
GO

CREATE PROCEDURE [ListLastAlarmForEachChannel] WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    SELECT AlarmTable.*
      ,CT.DevId
      ,CT.DevChn
      ,CT.EquipId
      ,CT.EquipPhase 
    FROM 
    (
        SELECT MAX([Key]) [Key] FROM [AlarmTable]
            GROUP BY ChannelKey
    ) Keys
    INNER JOIN [AlarmTable] ON [AlarmTable].[Key] = Keys.[Key]
    LEFT JOIN [ChannelTable] CT ON AlarmTable.ChannelKey = CT.[Key]

    RETURN
END
GO